﻿using System;
using System.Configuration;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;

/// <summary>
///class  SqlBooksProvider 
///  Provides access to SqlServer data source
/// </summary>
public class SqlEmailProvider : EmailProvider
{
    /// <summary>
    /// retrieves the connection string from the web.config
    /// </summary>
    //private string connectionString()
    //{
    //    SmallBusinessDataProvidersSection sec = (ConfigurationManager.GetSection("SmallBusinessDataProviders")) as SmallBusinessDataProvidersSection;
    //    string connectionStringName = sec.EmailProviders[sec.EmailProviderName].Parameters["connectionStringName"];
    //    return WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
    //}

    public static SqlConnection conn()
    {
        string sqlstr = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(sqlstr);
        return con;
    }
    SqlConnection con = conn();
    /// <summary>
    /// retrieves all email records
    /// </summary>
    public override List<Email> GetAllEmails()
    {
        List<Email> emailList = new List<Email>(); 
        // create a connection, invoke stored procedure
        //
        using (con)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetEmails", con);
            SqlDataReader r = cmd.ExecuteReader();
            Email curr;
            while (r.Read())
            {
                if (r["id"] is DBNull || r["email"] is DBNull)
                    throw new InvalidOperationException(Messages.EmailRequiredAttributesMissing);
                
                curr = new Email((string)r["email"]);
                curr.Needs = (r["needs"] is DBNull) ? String.Empty : (string)r["needs"];
                curr.Date = (r["date"] is DBNull) ? DateTime.MinValue : (DateTime)r["date"];   
                emailList.Add(curr);                       
            }
        }
        return emailList;
        }


    /// <summary>
    ///  用存储过程实现数据的插入
    /// </summary>
    /// <param name="txtemail"></param>
    /// <param name="txtneeds"></param>
    public void InsertEmail(string txtemail, string txtneeds)
    { 
       using (con)
        {
           con.Open();
          SqlCommand sqlCommand = new SqlCommand();
          sqlCommand.Connection = con;
          sqlCommand.CommandType = CommandType.StoredProcedure;
          sqlCommand.CommandText = "InsertEmail";

       //往存储过程中添加参数
        sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Bemail", System.Data.SqlDbType.NVarChar, 50));
        sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@needs", System.Data.SqlDbType.NVarChar,256));

       //给存储过程的参数付值
       sqlCommand.Parameters["@Bemail"].Value = txtemail.ToString();
       sqlCommand.Parameters["@needs"].Value = txtneeds.ToString();
       sqlCommand.ExecuteNonQuery();
       con.Close();
}

      
    
    }


   
   
}
